package com.erp.app.report.genericInquiry.inquirySp.dao;

import java.math.BigDecimal;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Hashtable;

import oracle.jdbc.driver.OracleTypes;

import com.erp.app.common.ProjectConstants;
import com.erp.app.report.genericInquiry.GenericInquiryConstants;
import com.erp.app.report.genericInquiry.inquiryCriteria.beans.vo.InquiryCriteriaVO;
import com.erp.app.report.genericInquiry.inquiryMaster.dao.GenericInquirySearchCriteria;
import com.erp.app.report.genericInquiry.inquiryMaster.ejb.GenericInquiryMasterPK;
import com.erp.app.report.genericInquiry.inquiryOutput.beans.vo.InquiryOutputVO;
import com.erp.app.report.genericInquiry.inquirySp.beans.vo.InquirySpVO;
import com.erp.framework.exceptions.FindRecordException;
import com.erp.framework.model.dao.SimplePageEditStyleDao;
import com.erp.framework.model.vo.ChildList;
import com.erp.framework.model.vo.SimpleBaseVo;
import com.erp.framework.util.logger.Logger;

public class InquirySpDAO extends SimplePageEditStyleDao
{
//  to be implemented when needed
    protected String sqlPurge(){return null;}

//  to be implemented when needed
    protected void setValuesForPurge(PreparedStatement pStmt, Object obj) throws SQLException{}

//  to be implemented when needed
    protected String sqlUpdate(){return null;}

//  to be implemented when needed
    protected void setValuesForModify(PreparedStatement pStmt, Object obj) throws SQLException{}
    
//  to be implemented when needed
    protected String sqlFind(){return null;}

//  to be implemented when needed
    protected void setValuesForFind(PreparedStatement pStmt, Object obj) throws SQLException{}

//  to be implemented when needed
    protected String sqlInsert(){return null;}

//  to be implemented when needed
    protected void setValuesForInsert(PreparedStatement pStmt, Object obj) throws SQLException{}

//  to be implemented when needed
    protected String sqlDelete(){return null;}

//  to be implemented when needed
    protected void setValuesForDelete(PreparedStatement pStmt, Object obj) throws SQLException{}


    protected String sqlList(){
        String sql = "select a.inquiry_type inquiry_type, nvl(b.label, a.inquiry_name) inquiry_name, a.sp_name sp_name, a.use_ref_cursor use_ref_cursor "
        	+ " , a.create_user create_user, a.create_date create_date, a.update_user update_user "
        	+ " , a.update_date update_date, a.pdf_flag pdf_flag, a.pdf_comp_font_size pdf_comp_font_size, a.pdf_title_font_size pdf_title_font_size "
        	+ " , a.pdf_content_font_size pdf_content_font_size "
        	+ " , a.pdf_total_font_size pdf_total_font_size, a.pdf_group_field pdf_group_field, a.pdf_page_size pdf_page_size "
        	+ " , a.pdf_page_rotate pdf_page_rotate, a.pdf_comp_logo pdf_comp_logo, a.maint_url maint_url, a.max_image_size max_image_size "
        	+ " from t_inquiry_sp a, t_inquiry_sp_label b "
        	+ " where a.inquiry_type = ? "
        	+ " and a.inquiry_type = b.inquiry_type (+) "
        	+ " and b.LANG (+) = ?  ";
        
        return sql;
    }
    
    protected SimpleBaseVo getResultSetValues(ResultSet rs) throws SQLException{
        InquirySpVO inquirySpVO = new InquirySpVO();
        inquirySpVO.setInquiryType(rs.getString("INQUIRY_TYPE"));
        inquirySpVO.setInquiryName(rs.getString("INQUIRY_NAME"));
        inquirySpVO.setSpName(rs.getString("SP_NAME"));
        inquirySpVO.setUseRefCursor(rs.getBigDecimal("USE_REF_CURSOR"));
        inquirySpVO.setCreateUser(rs.getString("CREATE_USER"));
        inquirySpVO.setCreateDate(rs.getTimestamp("CREATE_DATE"));
        inquirySpVO.setUpdateUser(rs.getString("UPDATE_USER"));
        inquirySpVO.setUpdateDate(rs.getTimestamp("UPDATE_DATE"));
        inquirySpVO.setPdfFlag(rs.getString("PDF_FLAG"));
        inquirySpVO.setPdfCompFontSize(rs.getBigDecimal("PDF_COMP_FONT_SIZE"));
        inquirySpVO.setPdfTitleFontSize(rs.getBigDecimal("PDF_TITLE_FONT_SIZE"));
        inquirySpVO.setPdfContentFontSize(rs.getBigDecimal("PDF_CONTENT_FONT_SIZE"));
        inquirySpVO.setPdfGroupField(rs.getBigDecimal("PDF_GROUP_FIELD"));
        inquirySpVO.setPdfTotalFontSize(rs.getBigDecimal("PDF_TOTAL_FONT_SIZE"));
        inquirySpVO.setPdfPageSize(rs.getString("PDF_PAGE_SIZE"));
        inquirySpVO.setPdfPageRotate(rs.getString("PDF_PAGE_ROTATE"));
        inquirySpVO.setPdfCompLogo(rs.getString("PDF_COMP_LOGO"));
        inquirySpVO.setMaintURL(rs.getString("MAINT_URL"));
        inquirySpVO.setMaxImageSize(rs.getBigDecimal("MAX_IMAGE_SIZE"));
        
        return inquirySpVO;
    }
    
    public ChildList selectByParent(SimpleBaseVo parent, Connection conn) throws FindRecordException {
		return super.selectByParent(parent, InquirySpVO.class, conn);
	}

    protected String toWhereClause(Object obj)
	{
		return "";
	}

	protected void setValuesForCriteria(PreparedStatement pStmt, Object obj)
	throws SQLException {
	}

	protected String sqlListWhereClause(Object bizModel)
	{
		return " WHERE INQUIRY_TYPE = ? ";
	}

	protected void setValuesForList(PreparedStatement pStmt, Object bizModel)
	throws SQLException
	{
	    int i = 1;
        GenericInquiryMasterPK pk = (GenericInquiryMasterPK) bizModel;
        System.out.print("*****************************, spDao, pk.getInquiryType()=" + pk.getInquiryType());
	    pStmt.setString(i++, pk.getInquiryType());
	    pStmt.setString(i++, pk.getLang());
	}

	public Hashtable createTable(GenericInquirySearchCriteria criteria, Connection conn)
		throws Exception
	{
	    Hashtable params = new Hashtable();

	    ChildList list = criteria.getCriteriaList();
	    StringBuffer marks = new StringBuffer();

	    for (int i = 0; i < list.size(); i++) {
	        InquiryCriteriaVO vo = (InquiryCriteriaVO)list.get(i);
	        marks.append(",?");
		    if (GenericInquiryConstants.FIELD_TYPE_TEXT_FM_TO.equals(vo.getFieldType())
		    		|| GenericInquiryConstants.FIELD_TYPE_TEXT_FM_TO_OC.equals(vo.getFieldType())
		    		|| GenericInquiryConstants.FIELD_TYPE_DROP_DOWN_FM_TO.equals(vo.getFieldType())){
		        marks.append(",?");
		    }
	    }

	    String RCMark = "";
	    if (criteria.isUseRefCursor()) {
	        RCMark = "?,";
	    }

	    String stmt = "{ call " + criteria.getSrchStoreProcName() + "("+RCMark+"?,?,?,?,?,?,?,?,?,?"+marks.toString()+") }";

  		ResultSet rs = null;
		ArrayList rslt = new ArrayList();
		CallableStatement cStmt = null;
		String reportTime = "";
	    int rptTimePos = -1;
	    int errPos = -1;
	    int RCPos = -1;
	    int filenamePos = -1;
	    int outQueryPos = -1;

		try {
			cStmt = conn.prepareCall(stmt);

			int i=1;
		    if (criteria.isUseRefCursor()) {
		        RCPos = i;
				cStmt.registerOutParameter(i++, OracleTypes.CURSOR);	//reference cursor
		    }
			rptTimePos = i;
			cStmt.registerOutParameter(i++, java.sql.Types.VARCHAR);	//reportTime
			cStmt.registerOutParameter(i++, java.sql.Types.DECIMAL);	//return code
			errPos = i;
			cStmt.registerOutParameter(i++, java.sql.Types.VARCHAR);	//err msg
			outQueryPos = i;
			cStmt.registerOutParameter(i++, java.sql.Types.VARCHAR);	//query
            filenamePos = i;
            cStmt.registerOutParameter(i++, java.sql.Types.VARCHAR);    //filename

			cStmt.setString(i++, criteria.getSrchCompanyCode().trim().toUpperCase());
			cStmt.setString(i++, criteria.getSrchUserCode().trim().toUpperCase());
			cStmt.setString(i++, criteria.getSrchInquiryType().trim());
			if (criteria.getSrchReport() == ProjectConstants.GENERIC_REPORT_TYPE_PDF) {
				cStmt.setInt(i++, -1);
			} else if (criteria.getSrchReport() == ProjectConstants.GENERIC_REPORT_TYPE_EXCEL) {
				cStmt.setInt(i++, ProjectConstants.EXCEL_ROW_NUM);
			} else if (criteria.getSrchReport() == ProjectConstants.GENERIC_REPORT_TYPE_HTML_TXT) {
				cStmt.setInt(i++, ProjectConstants.HTML_TXT_ROW_NUM);
			} else {
				cStmt.setInt(i++, ProjectConstants.MAX_DB_ROW);
			}

            // action
            cStmt.setString(i++, criteria.getAction());

            int disableCount = 0;
			for (int j = 0; j < list.size(); j++) {
			    InquiryCriteriaVO vo = (InquiryCriteriaVO)list.get(j);
			    
			    //Victor
			    if(criteria.getSrchFrom()==null){
			    	cStmt.setString(i++, null);
			    	continue;
			    }
			    if(criteria.getSrchTo()==null){
			    	cStmt.setString(i++, null);
			    	continue;
			    }
			    
			    if(GenericInquiryConstants.DATA_TYPE_DATE.equalsIgnoreCase(vo.getFieldNature())
			    		&& ((criteria.getSrchFrom()[j] == null)
			    			|| ("null".equalsIgnoreCase(criteria.getSrchFrom()[j])))){
		    		cStmt.setString(i++, null);
			    } else {
			    	cStmt.setString(i++, criteria.getSrchFrom()[j]);
			    }
			    if (GenericInquiryConstants.FIELD_TYPE_TEXT_FM_TO.equals(vo.getFieldType())
			    		|| GenericInquiryConstants.FIELD_TYPE_TEXT_FM_TO_OC.equals(vo.getFieldType())
			    		|| GenericInquiryConstants.FIELD_TYPE_DROP_DOWN_FM_TO.equals(vo.getFieldType())){
				    if(GenericInquiryConstants.DATA_TYPE_DATE.equalsIgnoreCase(vo.getFieldNature())
				    		&& ((criteria.getSrchTo()[j] == null)
				    			|| ("null".equalsIgnoreCase(criteria.getSrchTo()[j])))){
				    	cStmt.setString(i++, null);
				    } else {
				    	cStmt.setString(i++, criteria.getSrchTo()[j]);
				    }
			    }
			    
				Logger.field(this, "createTable(), criteria.getSrchFrom()[j]=" + criteria.getSrchFrom()[j]);
				Logger.field(this, "createTable(), criteria.getSrchTo()[j]=" + criteria.getSrchTo()[j]);
			}

			cStmt.execute();

			String errMsg = cStmt.getString(errPos);
			reportTime = cStmt.getString(rptTimePos);
			String filename = cStmt.getString(filenamePos);
			String outQuery = cStmt.getString(outQueryPos);
			
			Logger.sql(this, "outQuery=[" + outQuery + "]");

			if(errMsg != null){
				throw new SQLException(errMsg);
			}
			
		    if (criteria.isUseRefCursor()) {
		        ArrayList lsResult = new ArrayList();
		        rs = (ResultSet) cStmt.getObject(RCPos) ;
		        if (rs != null) {
		            ResultSetMetaData rsmd = rs.getMetaData();
		            int numOfCol = rsmd.getColumnCount();
		            int count = 0;
		            while (rs.next()) {
		        		InquiryOutputVO inquiryOutputVO = new InquiryOutputVO();
		        		inquiryOutputVO.setCompanyCode(criteria.getSrchCompanyCode().trim().toUpperCase());
		        		inquiryOutputVO.setInquiryType(criteria.getSrchInquiryType().trim());
		        		inquiryOutputVO.setRptTime(reportTime);
		        		inquiryOutputVO.setLineNo(new BigDecimal(count));

		        		ArrayList a = inquiryOutputVO.getFields(); 

		        	    for (int k=1; k<=numOfCol; k++) {
		        	    	Object obj = rs.getObject(k);

							if(obj != null){
								if(ProjectConstants.BYTE_ARRAY_CLASS_NAME.equals(obj.getClass().getName())){
									//byte array
									a.add((byte []) obj);
								} else {
									a.add(obj.toString());
								}
							} else {
								a.add(null);
							}
		        	    }

		        		lsResult.add(inquiryOutputVO);
		        		count++;
		            }
		        }
			    params.put(ProjectConstants.GENERIC_INQUIRY_PARAM_RESULT_LIST,lsResult);
		    }

		    params.put(ProjectConstants.GENERIC_INQUIRY_PARAM_RPT_TIME,reportTime);
            params.put(ProjectConstants.GENERIC_INQUIRY_PARAM_FILENAME, filename);
		} catch (Exception e) {
			try{
				if(cStmt != null){
					String errMsg = cStmt.getString(errPos);
					Logger.debug(this, "createTable(): errMsg=["+errMsg+"]");
				}
			} catch (Exception e1){
				
			}
		    throw e;
		} finally {
		    try {
		        if(rs != null) rs.close();
		    } catch (Exception e) {}
		    try {
				if (cStmt != null)
					cStmt.close();
			} catch (Exception e) {
				Logger.logExceptions(this, e);
			}
		}

		return params;
	}
}
